package wap.rockvilletech.portal;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import org.apache.log4j.Logger;

/**
 *
 * @author Tasnim
 */
public class UGreetingsBean {
	
    private static final Logger log=Logger.getLogger(UGreetingsBean.class);    

    public int saveCardSenderInfo(String strFrom, String strTo, String strRecGSM, String strContent) {
        int lastInsertId = -1;
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
//          int count = stmt.executeUpdate("INSERT INTO greetingcardsendinfo(ContentId,Sender,Receiver,Rcv_MSISDN,DateAndTime,Status) Values('" + strContent + "','" + strFrom + "','" + strTo + "','" + strRecGSM + "',NOW(),0)");
            PreparedStatement stmt = con.prepareStatement("INSERT INTO greetingcardsendinfo(ContentId,Sender,Receiver,Rcv_MSISDN,DateAndTime,Status) Values(?,?,?,?,NOW(),0)");
            int parameterIndex=0;
            stmt.setString(++parameterIndex, strContent);
            stmt.setString(++parameterIndex, strFrom);
            stmt.setString(++parameterIndex, strTo);
            stmt.setString(++parameterIndex, strRecGSM);
            int count = stmt.executeUpdate();
            if(count > 0) {	            
	            try {
		            ResultSet rs = stmt.executeQuery("select LAST_INSERT_ID()");
		            if(rs.next()) {
		            	lastInsertId = rs.getInt(1);
		            }
		            rs.close();
	            } catch (Exception e) {
	            	log.error("Error getting lastInsertID, saveCardSenderInfo(" + strFrom + ", " + strTo+ ", " + strRecGSM+ ", " + strContent + ") ", e);
                        lastInsertId = -1;
	            }
            }            
            stmt.close();
        } catch (Exception e) {
            log.error("saveCardSenderInfo(" + strFrom + ", " + strTo+ ", " + strRecGSM+ ", " + strContent + ") ", e);
            lastInsertId = -1;
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return lastInsertId;
    }//save CardSender Info
    
    public String [] getGreetingCardInfo(String cardId) {
        String [] cardInfo = null;
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
            Statement stmt = con.createStatement();
            //ContentId,Snd_MSISDN,Sender,Receiver,Rcv_MSISDN,DateAndTime,Status            
            ResultSet rs = stmt.executeQuery("select gc.ContentId,"+
				"(select c.CategoryID from content c where c.contentid=gc.contentid limit 1) CategoryID,"+
				"(select c.CategoryName from content c where c.contentid=gc.contentid limit 1) CategoryName,"+
				"gc.Sender,gc.Rcv_MSISDN from greetingcardsendinfo gc where gc.seqid=" + cardId);
	        if(rs.next()) {
	        	cardInfo = new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)};
	        }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.error("getGreetingCardInfo(" + cardId + ") ", e);
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return cardInfo;
    }//getGreetingCardInfo

    public boolean updateGreetingCardSenderInfo(String cardId, String senderGsm, int priceLogId, String wpSsendStatus) {
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
//            Statement stmt = con.createStatement();
//        	stmt.executeUpdate("update greetingcardsendinfo set Snd_MSISDN='"+senderGsm+"',chargeid="+priceLogId+",wappush_send_status='"+wpSsendStatus+"' where seqid=" + cardId);
            String sql = "update greetingcardsendinfo set Snd_MSISDN=?, chargeid=?, wappush_send_status=? where seqid=?";
            PreparedStatement stmt = con.prepareStatement(sql);
        	int parameterIndex=0;
            stmt.setString(++parameterIndex, senderGsm);
            stmt.setInt(++parameterIndex, priceLogId);
            stmt.setString(++parameterIndex, wpSsendStatus);
            stmt.setInt(++parameterIndex, Integer.parseInt(cardId));
            stmt.executeUpdate();        	
            stmt.close();
            return true;
        } catch (Exception e) {
            log.error("updateGreetingCardSenderInfo(" + cardId + ", "+senderGsm+""+priceLogId+") ", e);
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return false;
    }//save CardSender Info

    public int setUpdateStatus(String sid){
        String strQurery = "";
        int intReturn=0;
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
            Statement stmt = con.createStatement();
            strQurery ="update greetingcardsendinfo set Status=Status+1 where SeqId="+sid+"";
            //--System.out.println(strQurery);
            intReturn = stmt.executeUpdate(strQurery);           
            stmt.close();
        } catch (Exception e) {
            log.error("setUpdateStatus("+sid+") : " ,e);
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return intReturn;

    }//end Send Card Detail

    /**
     *
     * @param gsm
     * @param cid
     * @return
     */
    public ArrayList getSendCardDetail(String sid){
        String strQurery = "";
        ArrayList arrCategriez = new ArrayList();
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
            Statement stmt = con.createStatement();
            ResultSet rs;
            strQurery ="select gcInfo.Rcv_MSISDN,gcInfo.ContentId,gcInfo.Receiver,gcInfo.Sender,gcInfo.Status,gcInfo.chargeid"
                    +" from greetingcardsendinfo gcInfo"
                    +" where gcInfo.SeqId="+sid;
            rs = stmt.executeQuery(strQurery);
            if (rs.next()) {
                arrCategriez.add(rs.getString(1));
                arrCategriez.add(rs.getString(2));
                arrCategriez.add(rs.getString(3));
                arrCategriez.add(rs.getString(4));
                arrCategriez.add(rs.getString(5));
                arrCategriez.add(rs.getString(6));
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.error(" Get Card Categories : ",e);
            arrCategriez.clear();
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return arrCategriez;

    }//end Send Card Detail
    public Vector getCardCategories() {
        Vector arrCategriez = new Vector();
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
            String strQurery = "Select SeqId,FilmOrSinger,PageTitle from categories where Status=1 and IsAuthorized=1 and ParentCategoryId not like 0 and MainCat='UGreetings' order by Priority asc";
            PreparedStatement pst = con.prepareStatement(strQurery);
            ResultSet rs = pst.executeQuery(strQurery);
            while (rs.next()) {
                arrCategriez.add(new String[]{rs.getString(1),rs.getString(2),rs.getString(3)});
            }
            rs.close();
            pst.close();

        } catch (Exception e) {
            log.error(" Get Card Categories : " ,e);
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return arrCategriez;
    }//end getCardCategories
    
    /**
     * 
     * @param cid
     * @return
     */
    public ArrayList getCardDetail(String sid) {
        String strQurery = "";
        ArrayList arrCategriez = new ArrayList();
        Connection con = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            con = dbcon.getJNDIConnection();
            Statement stmt = con.createStatement();
            ResultSet rs;
            strQurery = "select ContentID,Point1,Point2,Bias,FillColor,originalFile,price from content where ContentID=(SELECT ContentID FROM greetingcardsendinfo where SeqId="+sid+" limit 1) and Status=1";

            rs = stmt.executeQuery(strQurery);
            while (rs.next()) {
                arrCategriez.add(rs.getString(1));
                arrCategriez.add(rs.getString(2));
                arrCategriez.add(rs.getString(3));
                arrCategriez.add(rs.getString(4));
                arrCategriez.add(rs.getString(5));
                arrCategriez.add(rs.getString(6));
                arrCategriez.add(rs.getString(7));
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            log.error(" getCardDetail("+sid+"): ", e);
        }
        finally{
            dbcon.closeJNDIConnection(con);
        }
        return arrCategriez;
    }//end get Card Content

    public Vector getCardContent(String pageNo, String catId) {
       Vector<Object> v = new Vector<Object>();
        Connection conn = null;
        DBProc dbcon = DBProc.getInstance();
        try {            
            conn = dbcon.getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_get_ugreetings(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setInt(2, Integer.parseInt(catId));
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3)});
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getCardContent PageNo=" + pageNo + " Catid=" + catId, e);
        } finally {
            dbcon.closeJNDIConnection(conn);
        }
        return v;
    }//end get Card Content
    
    public void LogVisit(String IP, String Page, String GSM, String APN, String Brand, String Model, String AppName, String queryString) {
        Connection con = null;
        DBProc db = DBProc.getInstance();
        try {
            String strQuery = "Insert into hits(IP,Page, AppName, QueryString, Brand, Model,GSM) values(?,?,?,?,?,?,?)";
            con = db.getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, IP);
            pst.setString(2, Page);
            pst.setString(3, AppName);
            pst.setString(4, queryString);
            pst.setString(5, Brand);
            pst.setString(6, Model);
            pst.setString(7, GSM);
            pst.executeUpdate();
            pst.close();
        } catch (Exception e) {
            log.error("LogVisits(" + IP + ", " + Page + ", " + GSM + ", " + APN + ", " + Brand + ", " + Model + ", " + AppName + ", " + queryString + ") ", e);
        } finally {
            db.closeJNDIConnection(con);
        }
    }
    
}
